package com.infinitegroup.findmystuff.models;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import com.infinitegroup.findmystuff.presenters.Constants;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * This class manages connections with the database.
 * 
 * @author Rohit Pathak
 */
public class DatabaseLink{
	
	protected static final String DATABASE_NAME = "fmsDB.db";
	protected static final String DATABASE_TABLE_USERS = "users";
	protected static final String DATABASE_TABLE_ITEMS = "items";
	
	private static final String DATABASE_CREATE_USERS = "CREATE TABLE "	+ DATABASE_TABLE_USERS + " (" 
			+ Constants.ACCOUNT_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " 
			+ Constants.ACCOUNT_FIRSTNAME + " TEXT NOT NULL, " 
			+ Constants.ACCOUNT_LASTNAME + " TEXT NOT NULL, "
			+ Constants.ACCOUNT_EMAIL + " VARCHAR NOT NULL, " 
			+ Constants.ACCOUNT_USERNAME + " VARCHAR NOT NULL, " 
			+ Constants.ACCOUNT_PASSWORD + " VARCHAR NOT NULL, "
			+ Constants.ACCOUNT_PHONE + " VARCHAR NOT NULL, " 
			+ Constants.ACCOUNT_ZIPCODE	+ " VARCHAR NOT NULL, "
			+ Constants.ACCOUNT_REQUESTED_ITEMS + " TEXT, "
			+ Constants.ACCOUNT_ACTIVE + " BOOLEAN, "
			+ Constants.ACCOUNT_ADMIN + " BOOLEAN); ";
	
	private static final String DATABASE_CREATE_ITEMS = "CREATE TABLE "	+ DATABASE_TABLE_ITEMS + " ( "
			+ Constants.ITEM_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ Constants.ITEM_ITEMNAME + " TEXT NOT NULL, "
			+ Constants.ITEM_LOCATION+" TEXT, "
			+ Constants.ITEM_DATE+ " DATETIME NOT NULL DEFAULT CURRENT_DATE, "
			+ Constants.ITEM_REWARD+" TEXT, "
			+ Constants.ITEM_DESCRIPTION+" TEXT, "
			+ Constants.ITEM_STATUS + " VARCHAR, " 
			+ Constants.ITEM_CATEGORY + " VARCHAR, "
			+ Constants.ITEM_REQUESTED_BY + " TEXT, "
			+ Constants.ACCOUNT_USERNAME+" TEXT NOT NULL, "
			+ "FOREIGN KEY("+Constants.ACCOUNT_USERNAME+") REFERENCES "+DATABASE_TABLE_USERS+"("+Constants.ACCOUNT_USERNAME+")); ";
	
	private static final String[] ALL_USER_COLUMNS = {Constants.ACCOUNT_ID, Constants.ACCOUNT_FIRSTNAME, 
		Constants.ACCOUNT_LASTNAME, Constants.ACCOUNT_EMAIL, Constants.ACCOUNT_USERNAME, 
		Constants.ACCOUNT_PASSWORD, Constants.ACCOUNT_PHONE, Constants.ACCOUNT_ZIPCODE, 
		Constants.ACCOUNT_ACTIVE, Constants.ACCOUNT_ADMIN, Constants.ACCOUNT_REQUESTED_ITEMS};
	
	private static final String[] ALL_ITEM_COLUMNS = {Constants.ITEM_ID, Constants.ITEM_ITEMNAME, 
		Constants.ITEM_LOCATION, Constants.ITEM_DATE, Constants.ITEM_REWARD, Constants.ITEM_DESCRIPTION, 
		Constants.ITEM_STATUS, Constants.ITEM_CATEGORY, Constants.ACCOUNT_USERNAME, Constants.ITEM_REQUESTED_BY};

	private static final int DATABASE_VERSION = 11;
	private static final String TAG = "DatabaseLink";

	private SQLiteDatabase db;
	private Context context;
	private DatabaseHelper dbHelper;

	/**
	 * Constructor.
	 * 
	 * @param context
	 */
	public DatabaseLink(Context context) {
		this.context = context;
	}

	/**
	 * Opens a connection with the database.
	 * 
	 * @return
	 * @throws SQLException
	 */
	public DatabaseLink open() throws SQLException {
		dbHelper = new DatabaseHelper(context);
		db = dbHelper.getWritableDatabase();
		return this;
	}

	/**
	 * Closes database connection.
	 */
	public void close() {
		dbHelper.close();
	}
	
	/**
	 * Inserts a new user account into the database
	 * Should be replaced by a more general insertStuff()
	 * @return the userid of the registered user. 
	 */
	public long insertUser(String firstName, String lastName, String email,
			String username, String password, String phone, String zipcode,
			boolean active, boolean admin) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(Constants.ACCOUNT_FIRSTNAME, firstName);
		initialValues.put(Constants.ACCOUNT_LASTNAME, lastName);
		initialValues.put(Constants.ACCOUNT_USERNAME, username);
		initialValues.put(Constants.ACCOUNT_PASSWORD, password);
		initialValues.put(Constants.ACCOUNT_EMAIL, email);
		initialValues.put(Constants.ACCOUNT_PHONE, phone);
		initialValues.put(Constants.ACCOUNT_ZIPCODE, zipcode);
		initialValues.put(Constants.ACCOUNT_ACTIVE, active);
		initialValues.put(Constants.ACCOUNT_ADMIN, admin);
		long id = db.insert(DATABASE_TABLE_USERS, null, initialValues);
		return id;
	}
	
	/**
	 * Update a specific entry in a table. It is very important to pass in the id of that entry. E.g. userid (NOT username) for users, item-id for items etc.
	 * @param table the table in which the entry is to be updated.
	 * @param id The unique id (type long) of that entry.
	 * @param column The column/attribute to be updated. Use protected final strings for consistency.
	 * @param newValue The new value to be inserted in place of the old one. 
	 */
	public void updateEntry(String table, String id, String column, String newValue){
		ContentValues newValues = new ContentValues();
		newValues.put(column, newValue);
		if (table.equals(DATABASE_TABLE_USERS)){
			db.update(table, newValues, Constants.ACCOUNT_ID +"='"+id+"'", null);
		} else if(table.equals(DATABASE_TABLE_ITEMS)){
			db.update(table, newValues, Constants.ITEM_ID+"='"+id+"'", null);
		}
	}
	
	/**
	 * Deletes an entry from the specified table in the database.
	 * @param table
	 * @param id - the unique row id of the item to be deleted.
	 */
	public void deleteEntry(String table, String id){
		db.delete(table, Constants.ACCOUNT_ID+"='"+id+"'", null);
	}

	/**
	 * Checks if an entry in a particular table exists.
	 * @param entry
	 * @param table
	 * @return
	 */
	public boolean entryExists(String entry, String table) {
		Cursor c = db.query(table, ALL_USER_COLUMNS, Constants.ACCOUNT_USERNAME+" = '"+entry+"'", null, null, null, null);
		if (c.moveToFirst()) {
			c.close();
			return true;
		}
		c.close();
		return false;
	}
	
	/**
	 * Gets a user from the database table and returns a map containing user details. Map format : [column --> details].
	 * @param username the username of the user to get the details of.
	 * @return Map containing all user details. Null if user doesn't exist.
	 */
	public HashMap<String, String> getUser(String username) {
		HashMap<String, String> map = new HashMap<String, String>();
		Cursor c = db.query(DATABASE_TABLE_USERS, ALL_USER_COLUMNS, Constants.ACCOUNT_USERNAME+"='"+username+"'", null, null, null, null);
		if (c.moveToNext()){
			c.moveToFirst();
			int columns = c.getColumnCount();
			for (int i = 0; (i < columns); i++){ // leave the last two columns as they are booleans.
				map.put(c.getColumnName(i), c.getString(i));
			}
			c.close();
			return map;
		}
		c.close();
		return null;
	}
	
	// This section onwards, the item table is handled.

		/**
		 * Inserts an item into the ITEMS table.
		 * Should be replaced by a more general insertStuff()
		 * @return
		 */
	public long insertItem(String itemname, String location, String reward, String description, 
			String status, String category, String username) {
		
			ContentValues initialValues = new ContentValues();
			SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
			Date dateNow = new Date();
			initialValues.put(Constants.ITEM_ITEMNAME, itemname);
			initialValues.put(Constants.ITEM_LOCATION, location);
			initialValues.put(Constants.ITEM_DATE, dateFormat.format(dateNow));
			initialValues.put(Constants.ITEM_REWARD, reward);
			initialValues.put(Constants.ITEM_DESCRIPTION, description);
			initialValues.put(Constants.ITEM_STATUS, status);
			initialValues.put(Constants.ITEM_CATEGORY, category);
			initialValues.put(Constants.ACCOUNT_USERNAME, username);
			return db.insert(DATABASE_TABLE_ITEMS, null, initialValues);
		}
	
	/**
	 * Get all items associated with a username. Null if the user has no items.
	 * @return
	 */
	public List<Map<String, String>> getItems(String username) {
		Map<String, String> itemMap;
		List<Map<String, String>> allItems = new ArrayList<Map<String,String>>();;
		Cursor c = db.query(DATABASE_TABLE_ITEMS, ALL_ITEM_COLUMNS, Constants.ACCOUNT_USERNAME+"='"+username+"'", null, null, null, null);
		while(c.moveToNext()){
			itemMap = new HashMap<String,String>();
			itemMap.put("itemid", c.getString(c.getColumnIndex(Constants.ITEM_ID)));
			itemMap.put("itemname", c.getString(c.getColumnIndex(Constants.ITEM_ITEMNAME)));
			itemMap.put("location", c.getString(c.getColumnIndex(Constants.ITEM_LOCATION)));
			itemMap.put("date", c.getString(c.getColumnIndex(Constants.ITEM_DATE)));
			itemMap.put("reward", c.getString(c.getColumnIndex(Constants.ITEM_REWARD)));
			itemMap.put("description", c.getString(c.getColumnIndex(Constants.ITEM_DESCRIPTION)));
			itemMap.put(Constants.ITEM_STATUS, c.getString(c.getColumnIndex(Constants.ITEM_STATUS)));
			itemMap.put(Constants.ITEM_CATEGORY, c.getString(c.getColumnIndex(Constants.ITEM_CATEGORY)));
			itemMap.put("username", c.getString(c.getColumnIndex(Constants.ACCOUNT_USERNAME)));
			allItems.add(itemMap);
		}
		c.close();
		return allItems;
	}
	
	/**
	 * 
	 * @param attribute the selection attribute of the user e.g. 
	 * @param value
	 * @return
	 */
	public List<Map<String, String>> getSelectedUsers(String attribute, String value){
		List<Map<String,String>> list = new LinkedList<Map<String,String>>();
		HashMap<String, String> map;
		Cursor c = db.query(DATABASE_TABLE_USERS, ALL_USER_COLUMNS, attribute + "='"+value+"'", null, null, null, null);
		int columns = c.getColumnCount();
		while (c.moveToNext()){
			map = new HashMap<String, String>();
			for (int i = 0; (i < columns); i++){ // leave the last two columns as they are booleans.
				map.put(c.getColumnName(i), c.getString(i));
			}
			//list.add(map);
			/*
			map.put(c.getColumnName(c.getColumnIndex(Constants.ACCOUNT_ID)), c.getString(c.getColumnIndex(Constants.ACCOUNT_ID)));
			map.put(c.getColumnName(c.getColumnIndex(Constants.ACCOUNT_FIRSTNAME)), c.getString(c.getColumnIndex(Constants.ACCOUNT_FIRSTNAME)));
			map.put(c.getColumnName(c.getColumnIndex(Constants.ACCOUNT_LASTNAME)), c.getString(c.getColumnIndex(Constants.ACCOUNT_LASTNAME)));
			map.put(c.getColumnName(c.getColumnIndex(Constants.ACCOUNT_USERNAME)), c.getString(c.getColumnIndex(Constants.ACCOUNT_USERNAME)));
			map.put(c.getColumnName(c.getColumnIndex(Constants.ACCOUNT_PASSWORD)), c.getString(c.getColumnIndex(Constants.ACCOUNT_PASSWORD)));
			map.put(c.getColumnName(c.getColumnIndex(Constants.ACCOUNT_EMAIL)), c.getString(c.getColumnIndex(Constants.ACCOUNT_EMAIL)));
			map.put(c.getColumnName(c.getColumnIndex(Constants.ACCOUNT_PHONE)), c.getString(c.getColumnIndex(Constants.ACCOUNT_PHONE)));
			map.put(c.getColumnName(c.getColumnIndex(Constants.ACCOUNT_ZIPCODE)), c.getString(c.getColumnIndex(Constants.ACCOUNT_ZIPCODE)));
			map.put(c.getColumnName(c.getColumnIndex(Constants.ACCOUNT_REQUESTED_ITEMS)), c.getString(c.getColumnIndex(Constants.ACCOUNT_REQUESTED_ITEMS)));
			map.put(c.getColumnName(c.getColumnIndex(Constants.ACCOUNT_ACTIVE)), c.getString(c.getColumnIndex(Constants.ACCOUNT_ACTIVE)));
			map.put(c.getColumnName(c.getColumnIndex(Constants.ACCOUNT_ADMIN)), c.getString(c.getColumnIndex(Constants.ACCOUNT_ADMIN)));
			*/
			list.add(map);
		}
		if (list.isEmpty()){
			return null;
		}
		return list;
	}
	
	public List<Map<String,String>> getItems(String attributes, String[] values, String orderBy){
		List<Map<String,String>> list = new LinkedList<Map<String,String>>();
		//System.out.println(orderBy);
		HashMap<String, String> map;
		Cursor c;
		c = db.query(DATABASE_TABLE_ITEMS, ALL_ITEM_COLUMNS, attributes, values, null, null, orderBy);
		int columns = c.getColumnCount();
		while (c.moveToNext()){
			map = new HashMap<String, String>();
			for (int i = 0; (i < columns); i++){ // leave the last two columns as they are booleans.
				map.put(c.getColumnName(i), c.getString(i));
			}
			list.add(map);
		}
		return list;
	}
	
	/**
	 * This class utilizes android's built in database functions. Helps to open
	 * a database connection. Credits: Professor Glenys Gustin, Delaware County
	 * Community College.
	 * 
	 */
	private class DatabaseHelper extends SQLiteOpenHelper {

		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			try {
				db.execSQL(DATABASE_CREATE_USERS);
				db.execSQL(DATABASE_CREATE_ITEMS);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
					+ newVersion + ", which will destroy all old data");
			db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE_USERS);
			db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE_ITEMS);
			onCreate(db);
		}
	}
	
	//deprecated methods
	//
	
	/*
	public String showEntireDatabase(){
		String result = " The database has ";
		Cursor c = db.query(DATABASE_TABLE_USERS, ALL_USER_COLUMNS, null, null, null, null, null);
		for (c.moveToFirst(); c.isAfterLast(); c.moveToNext()){
			result += c.getString(c.getColumnIndex(Constants.ACCOUNT_USERNAME)) + " " +c.getString(c.getColumnIndex(Constants.ACCOUNT_PASSWORD)) + " "+c.getString(c.getColumnIndex(Constants.ACCOUNT_EMAIL)) + "/n";
		}
		c.close();
		return result;
	}
	*/
	
	/*
	public String readData(String username){
		String result = "";
		Cursor c = db.query(DATABASE_TABLE_USERS, ALL_ITEM_COLUMNS, Constants.ACCOUNT_USERNAME +"='"+username+"'", null, null, null, null);
		for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()){
			result = result + c.getColumnCount() + " " + c.getString(c.getColumnIndex(Constants.ACCOUNT_FIRSTNAME)) + " " + c.getString(c.getColumnIndex(Constants.ACCOUNT_LASTNAME)) + " ";
		}
		c.close();
		return result;
	}
	*/

}
